﻿using BCCommon;
using BCEntity.TMS.MoveHouseConfig.MoveHouseConfig;
using BCEntity.TMS.MoveHouseConfig.MoveHouseSpecificationServiceConfig;
using BCEntity.TMS.MoveHouseConfig.MoveHouseSpecificationServiceConfigOverWrite;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.MoveHouseConfig.MoveHouseSpecificationServiceConfig
{
    public class MoveHouseSpecificationServiceConfigData : IMoveHouseSpecificationServiceConfigData
    {
        private readonly string SQL_MAIN_QUERY = @" SELECT
		                                            A.MoveHouseSpecificationServiceConfigId,
		                                            A.MoveHouseConfigId,
		                                            A.MoveHouseSpecificationConfigId,
		                                            A.SpecificationName,
		                                            A.SpecificationServiceConfigId,
		                                            A.SpecificationServiceName,
		                                            A.Remark,
		                                            A.Price,
		                                            A.Enabled,
		                                            A.CreateAdminId,
		                                            A.CreateAdminName,
		                                            A.CreateTime,
		                                            A.UpdateTime,
		                                            A.EditAdminId,
		                                            A.EditAdminName
                                                FROM
                                                    movehousespecificationserviceconfig A ";
        private readonly string SQL_DELETE = @" DELETE FROM movehousespecificationserviceconfig ";
        private readonly IDatabaseContext database;

        public MoveHouseSpecificationServiceConfigData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<MoveHouseSpecificationServiceConfigEntity> InsertAsync(MoveHouseSpecificationServiceConfigEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }

        public async Task<int> InsertAllAsync(IEnumerable<MoveHouseSpecificationServiceConfigEntity> entities)
        {
            return await this.database.Master.InsertAllAsync(entities, database.Transaction);
        }

        public async Task<int> DeleteBySpecificationConfigId(long moveHouseSpecificationConfigId)
        {
            var sql = $"{SQL_DELETE} WHERE MoveHouseSpecificationConfigId=?MoveHouseSpecificationConfigId";
            return await database.Master.ExecuteAsync(sql, new { moveHouseSpecificationConfigId });
        }

        public async Task<IEnumerable<MoveHouseSpecificationServiceConfigEntity>> GetList(long moveHouseSpecificationConfigId,
            bool? isEnable)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (isEnable.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnable.Value, DbType.Boolean);
            }
            wheres.Add("A.MoveHouseSpecificationConfigId = ?MoveHouseSpecificationConfigId");
            parameters.Add("?MoveHouseSpecificationConfigId", moveHouseSpecificationConfigId, DbType.Int64);
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $"{SQL_MAIN_QUERY} {whereSql} ORDER BY A.CreateTime DESC ";
            return await this.database.Slave.QueryAsync<MoveHouseSpecificationServiceConfigEntity>(sql, parameters);
        }

        public async Task<MoveHouseSpecificationServiceConfigEntity> GetSingle(long moveHouseSpecificationServiceConfigId)
        {
            var sql = typeof(MoveHouseSpecificationServiceConfigEntity).GetSelectSqlByPrimaryKeys();
            return await database.Slave.QueryFirstOrDefaultAsync<MoveHouseSpecificationServiceConfigEntity>(sql, new { moveHouseSpecificationServiceConfigId });
        }

        public async Task<int> IsExistService(long moveHouseServiceId)
        {
            var sql = @" SELECT COUNT(A.MoveHouseSpecificationServiceConfigId) 
                                FROM movehousespecificationserviceconfig A 
                                WHERE A.SpecificationServiceConfigId=?SpecificationServiceConfigId ";
            return await database.Slave.QueryFirstOrDefaultAsync<int>(sql, new { SpecificationServiceConfigId = moveHouseServiceId });
        }

        public async Task<MoveHouseSpecificationServiceConfigEntity> UpdateAysnc(MoveHouseSpecificationServiceConfigEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }

        public async Task<(IEnumerable<MoveHouseSpecificationServiceConfigViewEntity>, int)> GetAllPriceQuery(bool? enabled, int pageIndex,
int pageSize)
        {
            var sqlQuery = @" SELECT
                            A.MoveHouseSpecificationServiceConfigId,
                            NULL AS MoveHouseSpecificationServiceConfigOverWriteId,
                            C.MoveHouseConfigId AS ParentMoveHouseConfigId,
                            C.MoveHouseConfigName AS ParentMoveHouseConfigName,
                            B.MoveHouseConfigId AS MoveHouseConfigId,
                            B.MoveHouseConfigName AS MoveHouseConfigName,
                            A.MoveHouseSpecificationConfigId,
                            A.SpecificationName,
                            A.SpecificationServiceConfigId,
                            A.SpecificationServiceName,
                            A.Price,
                            A.Remark,
                            A.Enabled,
                            A.CreateAdminId,
                            A.CreateAdminName,
                            A.CreateTime,
                            A.UpdateTime,
                            A.EditAdminId,
                            A.EditAdminName
                        FROM
                        movehousespecificationserviceconfig A
                        INNER JOIN movehouseconfig B ON A.MoveHouseConfigId=B.MoveHouseConfigId
                        INNER JOIN movehouseconfig C ON B.ParentId=C.MoveHouseConfigId ";
            var sqlCount = @" SELECT COUNT(A.MoveHouseSpecificationServiceConfigId) 
                            FROM
                            movehousespecificationserviceconfig A
                            INNER JOIN movehouseconfig B ON A.MoveHouseConfigId=B.MoveHouseConfigId
                            INNER JOIN movehouseconfig C ON B.ParentId=C.MoveHouseConfigId ";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (enabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $@"{sqlQuery}{whereSql} ORDER BY A.MoveHouseSpecificationServiceConfigId 
                            LIMIT {(pageIndex - 1) * pageSize}, {pageSize};{sqlCount} {whereSql} ";
            return await database.Slave.QueryPageableListAsync<MoveHouseSpecificationServiceConfigViewEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<MoveHouseSpecificationServiceConfigOverWriteViewEntity>, int)> GetAllAgentPriceList(long agentRecordId, string provinceCode,
            string cityCode, string countyCode, bool? enabled, int pageIndex, int pageSize)
        {
            var sqlQuery = @" SELECT
                            A.MoveHouseSpecificationServiceConfigId,
                            A.MoveHouseSpecificationServiceConfigOverWriteId,
                            C.MoveHouseConfigId AS ParentMoveHouseConfigId,
                            C.MoveHouseConfigName AS ParentMoveHouseConfigName,
                            B.MoveHouseConfigId AS MoveHouseConfigId,
                            B.MoveHouseConfigName AS MoveHouseConfigName,
                            A.MoveHouseSpecificationConfigId,
                            A.SpecificationName,
                            A.SpecificationServiceConfigId,
                            A.SpecificationServiceName,
                            A.Price,
                            A.ProvinceCode,
						    E.ProvinceName,
                            A.CityCode,
							E.CityName,
                            A.CountyCode,
							E.CountyName,
                            A.AgentRecordId,
                            A.Remark,
                            A.Enabled,
                            A.CreateAdminId,
                            A.CreateAdminName,
                            A.CreateTime,
                            A.UpdateTime,
                            A.EditAdminId,
                            A.EditAdminName
                        FROM
                        movehousespecificationserviceconfigoverwrite A
                        INNER JOIN movehouseconfig B ON A.MoveHouseConfigId=B.MoveHouseConfigId
                        INNER JOIN movehouseconfig C ON B.ParentId=C.MoveHouseConfigId
						INNER JOIN county E ON E.CountyCode=A.CountyCode ";
            var sqlCount = @" SELECT COUNT(A.MoveHouseSpecificationServiceConfigOverWriteId) 
                            FROM
                            movehousespecificationserviceconfigoverwrite A
                            INNER JOIN movehouseconfig B ON A.MoveHouseConfigId=B.MoveHouseConfigId
                            INNER JOIN movehouseconfig C ON B.ParentId=C.MoveHouseConfigId
						    INNER JOIN county E ON E.CountyCode=A.CountyCode ";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (enabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            wheres.Add("A.AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Boolean);
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode = ?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode = ?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $@"{sqlQuery}{whereSql} ORDER BY A.MoveHouseSpecificationServiceConfigId 
                            LIMIT {(pageIndex - 1) * pageSize}, {pageSize};{sqlCount} {whereSql} ";
            return await database.Slave.QueryPageableListAsync<MoveHouseSpecificationServiceConfigOverWriteViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<MoveHouseSpecificationServiceConfigViewEntity>> GetListByMoveHouseConfigId(long moveHouseConfigId, bool? enabled)
        {
            var sqlQuery = @" SELECT
                            A.MoveHouseSpecificationServiceConfigId,
                            C.MoveHouseConfigId AS ParentMoveHouseConfigId,
                            C.MoveHouseConfigName AS ParentMoveHouseConfigName,
                            B.MoveHouseConfigId AS MoveHouseConfigId,
                            B.MoveHouseConfigName AS MoveHouseConfigName,
                            A.MoveHouseSpecificationConfigId,
                            A.SpecificationName,
                            A.SpecificationServiceConfigId,
                            A.SpecificationServiceName,
                            A.Price,
                            A.Remark,
                            A.Enabled,
                            A.CreateAdminId,
                            A.CreateAdminName,
                            A.CreateTime,
                            A.UpdateTime,
                            A.EditAdminId,
                            A.EditAdminName
                        FROM
                        movehousespecificationserviceconfig A
                        INNER JOIN movehouseconfig B ON A.MoveHouseConfigId=B.MoveHouseConfigId
                        INNER JOIN movehouseconfig C ON B.ParentId=C.MoveHouseConfigId ";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.MoveHouseConfigId = ?MoveHouseConfigId");
            parameters.Add("?MoveHouseConfigId", moveHouseConfigId, DbType.Int64);
            if (enabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $@"{sqlQuery}{whereSql} ORDER BY A.MoveHouseSpecificationServiceConfigId ";
            return await database.Slave.QueryAsync<MoveHouseSpecificationServiceConfigViewEntity>(sql, parameters);

        }
        public async Task<IEnumerable<MoveHouseMinPriceEntity>> GetMinPrice(bool? isEnable)
        {
            var sqlSelect = @" SELECT A.MoveHouseConfigId,MIN(A.Price) AS Price
                        FROM movehousespecificationserviceconfig A  ";
            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            if (isEnable.HasValue)
            {
                wheres.Add(" A.Enabled = ?Enabled ");
                parameters.Add("?Enabled", isEnable.Value, DbType.Boolean);
            }
            string sqlWhere = wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "";

            var sql = $"{sqlSelect} {sqlWhere} GROUP BY A.MoveHouseConfigId ";
            return await this.database.Slave.QueryAsync<MoveHouseMinPriceEntity>(sql, parameters);
        }

        public async Task<int> DeleteAsync(long moveHouseSpecificationServiceConfigId)
        {
            var sql = $"{SQL_DELETE} WHERE MoveHouseSpecificationServiceConfigId=?MoveHouseSpecificationServiceConfigId";
            return await database.Master.ExecuteAsync(sql, new { moveHouseSpecificationServiceConfigId }, database.Transaction);
        }

        public async Task<IEnumerable<MoveHouseSpecificationServiceConfigViewEntity>> GetList()
        {
            var sqlQuery = @" SELECT
                            A.MoveHouseSpecificationServiceConfigId,
                            NULL AS MoveHouseSpecificationServiceConfigOverWriteId,
                            C.MoveHouseConfigId AS ParentMoveHouseConfigId,
                            C.MoveHouseConfigName AS ParentMoveHouseConfigName,
                            B.MoveHouseConfigId AS MoveHouseConfigId,
                            B.MoveHouseConfigName AS MoveHouseConfigName,
                            A.MoveHouseSpecificationConfigId,
                            A.SpecificationName,
                            A.SpecificationServiceConfigId,
                            A.SpecificationServiceName,
                            A.Price,
                            A.Remark,
                            A.Enabled,
                            A.CreateAdminId,
                            A.CreateAdminName,
                            A.CreateTime,
                            A.UpdateTime,
                            A.EditAdminId,
                            A.EditAdminName
                        FROM
                        movehousespecificationserviceconfig A
                        INNER JOIN movehouseconfig B ON A.MoveHouseConfigId=B.MoveHouseConfigId
                        INNER JOIN movehouseconfig C ON B.ParentId=C.MoveHouseConfigId ";
            string sql = $@"{sqlQuery} ORDER BY A.MoveHouseSpecificationServiceConfigId ";
            return await database.Slave.QueryAsync<MoveHouseSpecificationServiceConfigViewEntity>(sql);
        }
    }
}
